<?
session_start();
include ('../funct.php');
$fir=$_POST['firma'];
$sql="select distinct DATE_FORMAT(wizyty.data, '%Y-%m')
from
pac, wizyty, sl_uslugi, sl_gab
where 
pac.id = wizyty.id_pac
and
wizyty.typ_wiz = sl_uslugi.id
and
sl_gab.id = wizyty.id_gab
and
sl_uslugi.id_firmy <> 0
and 
wizyty.akt = 1
order by wizyty.data desc
";

$result=mysql_query($sql);
$data[0]='Wybierz';
while($row = mysql_fetch_array($result))
{
$data[strtotime($row[0])]=$row[0];
}

$sql="select * from sl_firmy order by nazwa";
$result=mysql_query($sql);
$list_f[0]='Wybierz';
while($row = mysql_fetch_array($result))
{
$list_f[$row[0]]=$row[1];
}



if ($_POST['mies']){
$sz_zakres=date('Y-m', $_POST['mies']);
$sql="select pac.pac_imie, pac.pac_nazwisko, pac.pac_pesel, pac.polisa, sl_uslugi.nazwa, DATE_FORMAT(wizyty.data, '%Y-%m-%d'), sl_uslugi.cena, wizyty_inf_dod.nr_zlecenia, sl_uslugi.kod_proc, wizyty_inf_dod.icd10 from
pac, wizyty, sl_uslugi, sl_gab, wizyty_inf_dod
where 
pac.id = wizyty.id_pac
and
wizyty.typ_wiz = sl_uslugi.id
and
sl_gab.id = wizyty.id_gab
and
wizyty_inf_dod.id_wizyty = wizyty.id
and
sl_uslugi.id_firmy = ".$fir."
and 
wizyty.akt = 1
and 
wizyty.data like '".$sz_zakres."%'
order by wizyty.data
";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result))
{
$badania[]=$row;
}

$sql="select pac.pac_imie, pac.pac_nazwisko, pac.pac_pesel, pac.polisa, sl_lab_bad.nazwa, DATE_FORMAT(lab_zlec.data, '%Y-%m-%d'), sl_lab_bad.cena,  pac.zlecenie, 'brak' from
pac, lab_zlec, sl_lab_bad, lab_zlec_det
where 
pac.id = lab_zlec.id_pac
and
sl_lab_bad.id = lab_zlec_det.id_lab_bad
and
lab_zlec.id = lab_zlec_det.id_lab_zlec
and
lab_zlec.id_firmy = ".$fir."
and 
lab_zlec.akt = 1
and 
lab_zlec.data like '".$sz_zakres."%'
order by lab_zlec.data
";

$result=mysql_query($sql);
while($row = mysql_fetch_array($result))
{
$badanial[]=$row;
}


#poczatek excella
@unlink('../allianz/allianz'.$_COOKIE['PHPSESSID'].'.xls');
error_reporting(E_ALL);

/** PHPExcel */
require_once '../Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'Zestawienie za: '.date('Y-m', $_POST['mies']));
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', 'Lp.')
            ->setCellValue('B3', 'IMIĘ')
            ->setCellValue('C3', 'NAZWISKO')
            ->setCellValue('D3', 'PESEL')
            ->setCellValue('E3', 'NR POLISY')
            ->setCellValue('F3', 'NAZWA PROCEDURY UŻYWANA PRZEZ ALLIANZ')
            ->setCellValue('G3', 'KOD PROCEDURY')
            ->setCellValue('H3', 'DATA PROCEDURY')
            ->setCellValue('I3', 'CENA PROCEDURY')
            ->setCellValue('J3', 'NAZWA SPECJALISTY KIERUJĄCEGO NA BADANIE')
            ->setCellValue('K3', 'NR ZLECENIA')
            ->setCellValue('L3', 'ICD10');
$nr=4;
$nrl=1;
foreach ($badania as $i => $w ){
	
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$nr, $nrl);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$nr, $w[0]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$nr, $w[1]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$nr, $w[2]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$nr, $w[3]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$nr, $w[4]);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$nr, $w[8]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$nr, $w[5]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$nr, number_format($w[6]));
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$nr, '');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$nr, $w[7]);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L'.$nr, $w[9]);
	
	$nrl++;
	$nr++;
	
}

foreach ($badanial as $i => $w ){
	
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$nr, $nrl);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$nr, $w[0]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$nr, $w[1]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$nr, $w[2]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$nr, $w[3]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$nr, $w[4]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$nr, $w[5]);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$nr, $w[8]);
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$nr, number_format($w[6]));
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$nr, '');
	$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K'.$nr, $w[7]);
	
	$nrl++;
	$nr++;
	
}


$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);



$objPHPExcel->getActiveSheet()->setTitle('Darmedicus');

$objPHPExcel->setActiveSheetIndex(0);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('../allianz/allianz'.$_COOKIE['PHPSESSID'].'.xls');


#koniec excella
echo '<a href="../allianz/allianz'.$_COOKIE['PHPSESSID'].'.xls">Pobierz zestawienie</a>';
}
html();
echo '<form method=post action=wydr_allianz.php><table><tr><td>Zestawienie za okres:</td><td>';
form_auto_submit_2n('mies', $data, $_POST['mies']);
echo '</td></tr>';
echo '<tr><td>Płatnik:</td><td>';
form_auto_submit_2n('firma', $list_f, $_POST['firma']);
echo '</td></tr>';
echo '<tr><td colspan=2><input type="submit" value="Generuj" class="butt2"></td></tr>';
echo '</table></form>';

#zmienne();
?>